package com.pororo.smartoff;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SmartOffDBHelper extends SQLiteOpenHelper {
	final static String TAG = "SmartOffDBHelper";
	
	public SmartOffDBHelper(Context context) {
		super(context, DB_NAME, null, 1);
	}

	public static final String DB_NAME = "app_count.db";
	public static final String DB_TABLE = "app_count";

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + DB_TABLE + 
				" (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, count INTEGER DEFAULT 0, time INTEGER DEFAULT 0);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
		onCreate(db);
	}
	
	public static void increaseAppCount(Context context, String appName, int time) {
		SQLiteDatabase db = (new SmartOffDBHelper(context)).getWritableDatabase();
		db.execSQL("INSERT OR IGNORE INTO " + DB_TABLE + "(name, count, time) VALUES(\'" + appName + "\', 0, 0);");
		db.execSQL("UPDATE " + DB_TABLE + " SET count = count + 1, time = time + " + time + " WHERE name LIKE \'" + appName + "\';");		
	}
	
	public static void updateAppCountZero(Context context) {
		SQLiteDatabase db = (new SmartOffDBHelper(context)).getWritableDatabase();
		db.execSQL("UPDATE " + DB_TABLE + " SET count=0, time=0;");
	} 
	
	public static ArrayList<AppInfo> selectAppInfo(Context context) {
		ArrayList<AppInfo> list = new ArrayList<AppInfo>();
		SQLiteDatabase db = (new SmartOffDBHelper(context)).getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + DB_TABLE + " WHERE count > 0 ORDER BY time DESC;", null);
		
		while ( c.moveToNext() ) {
			list.add(new AppInfo(c.getString(1), c.getInt(2), c.getInt(3)));
		}
		
		return list;		
	}
}
